lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-08-28DB.txt

--数码表
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePieceHairPiece')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePieceHairPiece
END 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePieceHairPiece]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BView_DoorCityTotakePieceHairPiece]
AS
SELECT   
ID, OPlist_ViceNumber, OPlist_ProdName AS 商品名称, OPlist_ProdQuantity AS 数量, dbo.fn_CheckIsExpedited(OPlist_IsExpedited) AS 加急, OPlist_SendStatus, OPlist_SendVendor AS 发出, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_SendTime)) + ''/'' + dbo.fn_CheckUserIDGetUserName(OPlist_SendName) AS 发出日期, OPlist_SendName AS OPlist_SendNameCode, 
dbo.fn_CheckProductReworkStatus(OPlist_ReworkStatus) + CASE [OPlist_ReworkRemark] WHEN '''' THEN '''' ELSE ''['' + [OPlist_ReworkRemark] + '']'' END AS 返工, 
case  when OPlist_ReworQuantity is null then '''' else case when OPlist_ReworQuantity = 0 then '''' else cast(OPlist_ReworQuantity as varchar(10)) end end AS 返工数量,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_ReworkTime)) + ''/'' + dbo.fn_CheckUserIDGetUserName(OPlist_ReworName) AS 返工日期, 
OPlist_ReworName AS OPlist_ReworNameCode, OPlist_CompletedStatus, dbo.fn_CheckProductCompletedStatus(OPlist_CompletedStatus) AS 完成, 
--OPlist_CompletedQuantity AS 完成数量,
case  when OPlist_CompletedQuantity is null then '''' else case when OPlist_CompletedQuantity = 0 then '''' else cast(OPlist_CompletedQuantity as varchar(10)) end end AS 完成数量,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_CompletedTime)) + ''/'' + dbo.fn_CheckUserIDGetUserName(OPlist_CompletedName) AS 完成日期, 
OPlist_CompletedName AS OPlist_CompletedNameCode, dbo.fn_CheckTakePiecesStatus(OPlist_PickupStatus) AS 取走, 
--OPlist_PickupQuantity AS 取件数量,
case  when OPlist_PickupQuantity is null then '''' else case when OPlist_PickupQuantity = 0 then '''' else cast(OPlist_PickupQuantity as varchar(10)) end end AS 取走数量,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) + ''/'' + dbo.fn_CheckUserIDGetUserName(OPlist_PickupName) AS 取走日期, 
OPlist_PickupName AS OPlist_PickupNameCode
FROM      dbo.tb_ErpOrderProductList
WHERE   (OPlist_Type = ''2'')
' 
GO
---订单商品表
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderProductList') and name='OPlist_PickupQuantity')
begin
	alter table tb_ErpOrderProductList add OPlist_PickupQuantity  int Null
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderProductList', N'COLUMN',N'OPlist_PickupQuantity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'取件数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderProductList', @level2type=N'COLUMN',@level2name=N'OPlist_PickupQuantity'
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderProductList') and name='OPlist_ReworQuantity')
begin
	alter table tb_ErpOrderProductList add OPlist_ReworQuantity  int Null
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderProductList', N'COLUMN',N'OPlist_ReworQuantity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'返工数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderProductList', @level2type=N'COLUMN',@level2name=N'OPlist_ReworQuantity'
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderProductList') and name='OPlist_CompletedQuantity')
begin
	alter table tb_ErpOrderProductList add OPlist_CompletedQuantity  int Null
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderProductList', N'COLUMN',N'OPlist_CompletedQuantity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'完成数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderProductList', @level2type=N'COLUMN',@level2name=N'OPlist_CompletedQuantity'
GO

----摄控本（拍摄客人）
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainPhotographed')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainPhotographed
END 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainPhotographed]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BView_CameraControlBookMainPhotographed]
AS
SELECT  tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrdersPhotography.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 

Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,

dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
(Ord_SeriesPrice - (Case when (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) is null then 0 else (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) end) + (Select sum(Plu_Amount) as Plu_Amount from tb_ErpPlusPickItems where Plu_OrdNumber = Ord_Number)) AS 欠款,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
dbo.fn_CheckPhotographyStatus(Ordpg_PhotographyStatus) AS 拍摄状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime)) AS 摄影时间, 
Ordpg_ReservationPhotographyTime AS 摄影时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyName) AS 摄影师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyAssistant) AS 摄影助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationBootDivision) AS 引导师, 
dbo.tb_ErpOrdersPhotography.Ordpg_Remark AS 录入备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_Dispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_DispatchTime)) AS 安排时间,
(SELECT COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN
dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber WHERE (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO